package com.housekeeping.housekeeping.advertisingmanagement.dao.impl;/*
 * @author  pyh
 * @date  2021/1/23 0023 下午 5:11
 * @version 1.0
 * @since JDK1.8_241
 */

import com.housekeeping.housekeeping.advertisingmanagement.dao.ProductOrderDao;
import com.housekeeping.housekeeping.advertisingmanagement.entity.ProductOrder;
import com.housekeeping.housekeeping.utils.DBManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductOrderDaoImpl implements ProductOrderDao {
    //分页查询
    @Override
    public List<ProductOrder> getPagesList(int currentPage, int pageSize) throws SQLException {
        ArrayList<ProductOrder> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ProductOrder orders = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from product_statistics limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, currentPage);
            pStmt.setInt(2, pageSize);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new ProductOrder();
                orders.setId(rs.getInt("id"));
                orders.setNumber(rs.getInt("number"));
                orders.setName(rs.getString("name"));
                orders.setRename(rs.getString("rename"));
                orders.setPhone(rs.getString("phone"));
                orders.setNames(rs.getString("names"));
                orders.setPrice(rs.getDouble("price"));
                orders.setTime(rs.getDate("time"));
                orders.setStatus(rs.getInt("status"));
                list.add(orders);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }
    //查询总条数
    @Override
    public int getTotalCount() throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        int totalCount = 0;
        try {
            String sql = "select count(*) from product_statistics";
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, stmt, conn);
        }
        return totalCount;
    }
    // 查询订单记录
    @Override
    public ProductOrder getOrderContent(String number) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pStmt = null;
        conn = DBManager.getConnection();
        ProductOrder orders = null;
        try {
            String sql = "select * from product_statistics where id = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, number);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders.setId(rs.getInt("id"));
                orders.setNumber(rs.getInt("number"));
                orders.setName(rs.getString("name"));
                orders.setRename(rs.getString("rename"));
                orders.setPhone(rs.getString("phone"));
                orders.setNames(rs.getString("names"));
                orders.setPrice(rs.getDouble("price"));
                orders.setTime(rs.getDate("time"));
                orders.setStatus(rs.getInt("status"));
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return orders;
    }
    // 通过支付状态查询对应状态的订单
    @Override
    public List<ProductOrder> getStatusList(int status, int page, int size) throws SQLException {
        ArrayList<ProductOrder> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ProductOrder orders = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from product_statistics where status = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setInt(2, page);
            pStmt.setInt(3, size);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new ProductOrder();
                orders.setId(rs.getInt("id"));
                orders.setNumber(rs.getInt("number"));
                orders.setName(rs.getString("name"));
                orders.setRename(rs.getString("rename"));
                orders.setPhone(rs.getString("phone"));
                orders.setNames(rs.getString("names"));
                orders.setPrice(rs.getDouble("price"));
                orders.setTime(rs.getDate("time"));
                orders.setStatus(rs.getInt("status"));
                list.add(orders);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }
    // 获取支付状态查询对应状态的订单的总条数（用于前端的分页查询）
    @Override
    public int getStatusCount(int status) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from product_statistics where status = ?";
            conn = DBManager.getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            rs = pStmt.executeQuery();

            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
    }
    // 通过【手机号】和【支付状态】查询对应的订单
    @Override
    public List<ProductOrder> getPhonesList(String phone, int status, int page, int size) throws SQLException {
        ArrayList<ProductOrder> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ProductOrder orders = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from product_statistics where status = ? and phone = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setString(2, phone);
            pStmt.setInt(3, page);
            pStmt.setInt(4, size);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new ProductOrder();
                orders.setId(rs.getInt("id"));
                orders.setNumber(rs.getInt("number"));
                orders.setName(rs.getString("name"));
                orders.setRename(rs.getString("rename"));
                orders.setPhone(rs.getString("phone"));
                orders.setNames(rs.getString("names"));
                orders.setPrice(rs.getDouble("price"));
                orders.setTime(rs.getDate("time"));
                orders.setStatus(rs.getInt("status"));

                list.add(orders);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }
    // 通过【手机号】和【支付状态】查询对应的订单的总条数（用于前端分页）
    @Override
    public int getStatusAndPhoneCount(int status, String phone) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        int totalCount = 0;
        try {
            conn = DBManager.getConnection();
            String sql = "select count(*) from product_statistics where status = ? and phone = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setString(2, phone);
            rs = pStmt.executeQuery();
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return totalCount;
    }
}
